# -*- coding: utf-8 -*-
import pymongo
# client = pymongo.MongoClient('47.75.39.50')
client = pymongo.MongoClient('47.75.39.50')
client.admin.authenticate("admin321", "dsf::6666,,<<", mechanism='SCRAM-SHA-1')
db = client.city4
import re
import MySQLdb
import MySQLdb.cursors
from zlib import crc32
import pprint
conn = MySQLdb.connect("127.0.0.1","root","123456789", "shantianjun",charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor)
sql = "select * from area"
cursor = conn.cursor()
cursor.execute(sql)
area = cursor.fetchallDict()
# for x in area:
#     areas[x["level"]].append()
sql = "select name from mode union select name from cate"
cursor.execute(sql)
cateLsit = cursor.fetchall()
cateLsit = "|".join([x["name"] for x in cateLsit])
cursor.execute("select * from cate")
cate = cursor.fetchallDict()
cursor.execute("select * from mode")
mode = cursor.fetchallDict()
data = {}
reg = re.compile(r"\s(\S+)\((\S+)/")
emails = {}
for y in db.email.find():
    emails[y["storesUrl"]] = y["email"]
urlpool = set()
# import ipdb;ipdb.set_trace()
for y in db.stores.find():
        if y["storesUrl"] in urlpool:
            print "continue"
            continue
        data = {}
        data["url"] = y["storesUrl"].replace("https://www.cityheaven.net","")
        data["workTime"] = y["workTime"][1] if type(y["workTime"]) == list else y["workTime"]
        data["name"] = y["name"]
        data["email"] = emails.get(y["storesUrl"])
        data["address"] = re.sub("\[.+\]","",y["address"] or "")
        xx = "派遣エリア：".decode("utf")
        data["address"] = data["address"].replace(xx,"")
        data["address"] = "" if len(data["address"]) > 200 else data["address"]
        if y.get("phone"):
            data["phone"] = y.get("phone").strip()
        if y.get("site"):
            data["site"] = ",".join(y["site"]) if type(y["site"]) == list else y["site"]
        else:
            data["site"] = ""
        data["goto"] = "" if type(y.get("goto")) == list else y.get("goto")
        data["info"] = y["info"]
        if not y["cate2"] and not y["cate1"]:
            data["cate"] = "dispatch"
            data["mode"] = "dispatch"
        else:
            data["cate"] = y["cate2"]
            data["mode"] = y["cate1"]
        if not y["cate1"] and y["cate2"].find("(") > 0:
            if reg.search(y["cate2"]):
                data["mode"] = reg.search(y["cate2"]).groups()[0]
                data["cate"] = reg.search(y["cate2"]).groups()[1]
        data["price"] = y["price"]
        data['prix'] = 0
        data['type'] = y["type"]
        if y["price"]:
            try:
                prix = re.search(r'(\d+)\D+(\d+)',y['price']).groups()
                data['prix'] = int(prix[1])/int(prix[0]) * 60
            except:
                pass

        # data["urlHash"] = str(crc32(y["storesUrl"])).replace("-","")
        for yy in cate:
            if yy["name"] == data["cate"]:
                data["cateId"] = yy["id"]
        for yy in mode:
            if yy["name"] == data["mode"]:
                data["modeId"] = yy["id"]
        if y["area"]:
            for yy in area:
                if yy["name"] == re.sub(cateLsit,"",y["area"][1]) and yy["level"] == 1:
                    data['area'] = yy["name"]
                elif yy["name"] == re.sub(cateLsit,"",y["area"][3]) and yy["level"] == 2:
                    data['city'] = yy["name"]
                elif yy["name"] == re.sub(cateLsit,"",y["area"][4]) and yy["level"] == 3:
                    data['town'] = yy["name"]
        # import ipdb;ipdb.set_trace()
        fields = tuple(data.keys())
        fields = str(tuple(fields)).replace("'",'')
        data_dict = data.values()
        data_values = "(" + "%s," * (len(data_dict)) + ")"
        data_values = data_values.replace(',)', ')')
        sql = "insert into store_bak %s values %s" % (fields,data_values)
        print data_dict
        cursor.execute(sql,tuple(data_dict))
        urlpool.add(y["storesUrl"])
        # print sql
cursor.close()
conn.commit()
# insert ignore into cate (name) select distinct(cate) from store where cate <> ""
# insert ignore into mode (name) select distinct(mode) from store where mode <> ""
# update store set sonstatus = 0 where id not in ( select distinct(sid) from girl)